const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const BACKUP_PATH = path.join(process.cwd(), 'sql', 'backup', 'full-backup.sql');

console.log('🔄 精确恢复实例表和实例数据');
console.log('=============================');

try {
  if (!fs.existsSync(BACKUP_PATH)) {
    console.log('❌ 备份文件不存在:', BACKUP_PATH);
    return;
  }
  
  const backupContent = fs.readFileSync(BACKUP_PATH, 'utf8');
  console.log('✅ 成功读取备份文件');
  
  // 1. 提取所有实例相关的SQL语句
  const lines = backupContent.split('\\n');
  const instanceStatements = [];
  
  for (let i = 0; i < lines.length; i++) {
    const line = lines[i];
    
    // 查找实例表创建语句
    if (line.includes('CREATE TABLE device_instances_') || 
        line.includes('CREATE TABLE instance_params_')) {
      
      // 提取完整的CREATE TABLE语句
      let createStatement = line;
      let j = i + 1;
      
      // 继续读取直到找到完整的CREATE语句结束
      while (j < lines.length && !createStatement.includes(');')) {
        createStatement += '\\n' + lines[j];
        j++;
      }
      
      instanceStatements.push(createStatement);
      console.log('📋 找到表定义:', line.match(/CREATE TABLE (\\S+)/)?.[1]);
      i = j; // 跳过已处理的行
    }
    
    // 查找INSERT语句
    else if (line.includes('INSERT INTO "device_instances_') || 
             line.includes('INSERT INTO "instance_params_') ||
             line.includes('INSERT INTO device_instances_') ||
             line.includes('INSERT INTO instance_params_')) {
      instanceStatements.push(line);
    }
  }
  
  console.log(`📊 提取到 ${instanceStatements.length} 条实例相关语句`);
  
  // 2. 连接数据库并开始恢复
  const db = new Database(DB_PATH);
  
  console.log('\\n🔄 开始恢复实例表和数据...');
  
  let createdTables = 0;
  let insertedRecords = 0;
  let errors = 0;
  
  for (const statement of instanceStatements) {
    const trimmedStatement = statement.trim();
    if (!trimmedStatement) continue;
    
    try {
      db.prepare(trimmedStatement).run();
      
      if (trimmedStatement.includes('CREATE TABLE')) {
        createdTables++;
        const tableName = trimmedStatement.match(/CREATE TABLE (\\S+)/)?.[1];
        console.log(`  ✅ 创建表: ${tableName}`);
      } else if (trimmedStatement.includes('INSERT INTO')) {
        insertedRecords++;
        if (insertedRecords <= 10) { // 只显示前10条
          const tableName = trimmedStatement.match(/INSERT INTO "?(\\w+)"?/)?.[1];
          console.log(`  📝 插入数据到: ${tableName}`);
        }
      }
    } catch (error) {
      if (!error.message.includes('already exists') && 
          !error.message.includes('UNIQUE constraint failed')) {
        console.log(`  ❌ 执行失败: ${trimmedStatement.substring(0, 80)}...`);
        console.log(`     错误: ${error.message}`);
        errors++;
      }
    }
  }
  
  console.log(`\\n💾 数据插入完成 (显示了前10条，实际插入: ${insertedRecords}条)`);
  
  // 3. 验证恢复结果
  console.log('\\n📊 恢复结果验证');
  console.log('===================');
  
  // 查找所有实例相关表
  const instanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND (name LIKE 'device_instances_%' OR name LIKE 'instance_params_%')
    ORDER BY name
  `).all();
  
  console.log(`✅ 恢复表总数: ${createdTables} 个`);
  console.log(`📝 插入记录总数: ${insertedRecords} 条`);
  console.log(`❌ 错误数: ${errors} 个`);
  console.log(`📋 当前实例相关表: ${instanceTables.length} 个`);
  
  // 分类显示表
  const deviceInstanceTables = instanceTables.filter(t => t.name.startsWith('device_instances_'));
  const paramTables = instanceTables.filter(t => t.name.startsWith('instance_params_'));
  
  console.log(`\\n📋 主实例表 (${deviceInstanceTables.length}个):`);
  let totalInstances = 0;
  
  for (const table of deviceInstanceTables) {
    try {
      const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
      totalInstances += count.count;
      console.log(`  📊 ${table.name}: ${count.count} 个实例`);
    } catch (error) {
      console.log(`  ❌ 无法查询 ${table.name}: ${error.message}`);
    }
  }
  
  console.log(`\\n📋 参数表 (${paramTables.length}个):`);
  let totalParams = 0;
  
  for (const table of paramTables) {
    try {
      const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
      totalParams += count.count;
      if (count.count > 0) {
        console.log(`  📊 ${table.name}: ${count.count} 条参数记录`);
      }
    } catch (error) {
      console.log(`  ❌ 无法查询 ${table.name}: ${error.message}`);
    }
  }
  
  console.log(`\\n📊 数据恢复汇总:`);
  console.log(`  设备实例总数: ${totalInstances} 个`);
  console.log(`  参数记录总数: ${totalParams} 条`);
  
  if (totalInstances > 0 || totalParams > 0) {
    console.log('\\n🎉 成功恢复您的实例数据！');
    console.log('抱歉之前的错误操作，您的数据现在已经安全恢复。');
    console.log('\\n💡 说明:');
    console.log('- 原始系统使用分离式实例表结构（主表+参数表）');
    console.log('- 现在同时保留了原始结构和新的整合式结构');
    console.log('- 您可以继续使用原有的实例数据');
  } else {
    console.log('\\n⚠️  未发现实例数据，可能备份中没有包含实例记录');
  }
  
  db.close();
  
} catch (error) {
  console.error('❌ 恢复失败:', error.message);
  console.error(error.stack);
}